<?php
class Subscriptions extends Dao
{
	private static $_instance = null;
	protected $_db = null, $_cache = null;
	
	protected function __construct()
	{
		parent::__construct();
	}
	
	public static function getInstance() 
    {
        if (null === self::$_instance) {
            self::$_instance = new self();
        }
        return self::$_instance;
    }
	
	public function getListMailingLists(array $args = array(), $intPage = 1, $intLimit = MIN_ROWS_PER_PAGE)
	{
		try{
			$defaults = array(	
				/*pagination*/
				'page' => 1,
				'limit' => MIN_ROWS_PER_PAGE,
				'order_by' => 'list_position',
				'order' => 'DESC'	
			);
			$args = $this->args_merge($defaults, $args);
			
			$intPage = $args['page'];
			$intLimit = $args['limit'];
			$intStart = ($intPage-1)*$intLimit;		

			$cmd = $this->_db->createCommand('SELECT SQL_CALC_FOUND_ROWS list_id, list_name, list_description, list_color, list_position, list_status
 											  FROM tbl_subscription_lists
											  ORDER BY list_position DESC
											  LIMIT :intStart, :intLimit');
			$cmd->bindParam(":intStart",$intStart,PDO::PARAM_INT);
			$cmd->bindParam(":intLimit",$intLimit,PDO::PARAM_INT);	
			$arrResults = $cmd->queryAll();
			if(!empty($arrResults)){
				$arrResults[0]['total_rows'] = $this->_db->createCommand('SELECT FOUND_ROWS();')->queryScalar();
			}
			return $arrResults;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function getMailingList($listId)
	{	
		if(empty($listId)) throw new Exception('Invalid ListId');
		try{
			$strSql = " SELECT *
						FROM tbl_subscription_lists
						WHERE list_id=:id";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$listId,PDO::PARAM_STR);
			return $arrResults = $cmd->queryRow();
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function insertMailingList(array $args)
	{
		try{
			$defaults = array(
				'list_name' => null, 
				'list_description' => null,
				'list_color' => null, 
				'list_position' => null,
				'list_status' => null
			);
			$args = $this->args_merge($defaults, $args);
			
			$cmd = $this->_db->createCommand();
			$cmd->insert('tbl_subscription_lists',$args);
			return Yii::app()->db->getLastInsertId();
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function updateMailingList($listId, array $args)
	{
		if(empty($listId)) throw new Exception('Invalid ListId');
		try{
			$defaults = array(
				'list_name' => null, 
				'list_description' => null, 
				'list_color' => null,
				'list_position' => null,
				'list_status' => null
			);
			$args = $this->args_merge($defaults, $args);
			
			$cmd = $this->_db->createCommand();
			return $cmd->update('tbl_subscription_lists',$args, 'list_id=:id', array(':id'=>$listId));
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function deleteMailingList($listId)
	{
		if(empty($listId)) throw new Exception('Invalid ListId');
		try{
			$cmd = $this->_db->createCommand();
			//delete from subscribers
			$cmd->delete('tbl_subscription_subscriber_list', 'list_id=:id', array(':id'=>$listId));
			//delete from mails
			$cmd->delete('tbl_subscription_mail_list', 'list_id=:id', array(':id'=>$listId));
			//delete list
			return $cmd->delete('tbl_subscription_lists', 'list_id=:id', array(':id'=>$listId));			
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function getListSubscribers(array $args = array(), $intPage = 1, $intLimit = MIN_ROWS_PER_PAGE)
	{
		try{
			$defaults = array(
				'list_id' => null
			);
			$args = $this->args_merge($defaults, $args);
			
			$intStart = ($intPage-1)*$intLimit;
			
			$cmd = $this->_db->createCommand('SELECT SQL_CALC_FOUND_ROWS subscriber_id, subscriber_email, subscriber_name, subscriber_description, subscriber_created_date, subscriber_last_modified_date, subscriber_status
 											  FROM tbl_subscription_subscribers
											  ORDER BY subscriber_id DESC
											  LIMIT :intStart, :intLimit');
			$cmd->bindParam(":intStart",$intStart,PDO::PARAM_INT);
			$cmd->bindParam(":intLimit",$intLimit,PDO::PARAM_INT);	
			$arrResults = $cmd->queryAll();
			if(!empty($arrResults)){
				$arrResults[0]['total_rows'] = $this->_db->createCommand('SELECT FOUND_ROWS();')->queryScalar();
			}
			return $arrResults;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function getSubscriber($subscriber_id)
	{
		if(empty($subscriber_id)) throw new Exception('Invalid data');
		try{
			$strSql = " SELECT *
						FROM tbl_subscription_subscribers
						WHERE subscriber_id=:id OR subscriber_email=:id";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$subscriber_id,PDO::PARAM_STR);
			$arrResults = $cmd->queryRow();
			
			if(!empty($arrResults)){
				$arrResults['lists'] = $this->getListBySubscriber($subscriber_id);	
			}
			
			return $arrResults;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function insertSubscriber(array $args)
	{		
		try{
			$defaults = array(
				'subscriber_email' => null, 
				'subscriber_name' => null, 
				'subscriber_description' => null, 
				'subscriber_created_date' =>  new CDbExpression('NOW()'), 
				'subscriber_status' => 0
			);
			$args = $this->args_merge($defaults, $args);
			
			$cmd = $this->_db->createCommand();
			$cmd->insert('tbl_subscription_subscribers',$args);
			return Yii::app()->db->getLastInsertId();
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function updateSubscriber($subscriber_id, array $args)
	{
		if(empty($subscriber_id)) throw new Exception('Invalid data');
		try{
			$defaults = array(
				'subscriber_email' => null, 
				'subscriber_name' => null, 
				'subscriber_description' => null, 
				'subscriber_last_modified_date' => new CDbExpression('NOW()'), 
				'subscriber_status' => 0	
			);
			$args = $this->args_merge($defaults, $args);
			
			$cmd = $this->_db->createCommand();
			return $cmd->update('tbl_subscription_subscribers',$args, 'subscriber_id=:id', array(':id'=>$subscriber_id));
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function deleteSubscriber($subscriber_id)
	{
		if(empty($subscriber_id)) throw new Exception('Invalid data');
		try{
			$cmd = $this->_db->createCommand();
			//delete from list
			$cmd->delete('tbl_subscription_mail_list', 'subscriber_id=:id', array(':id'=>$listId));
			//delete subscriber
			return $cmd->delete('tbl_subscription_subscribers', 'subscriber_id=:id', array(':id'=>$subscriber_id));			
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function getListBySubscriber($subscriber_id)
	{
		if(empty($subscriber_id)) throw new Exception('Invalid data');
		try{
			$strSql = " SELECT sl.list_id, sl.status
						FROM tbl_subscription_subscriber_list sl INNER JOIN tbl_subscription_lists l ON (l.list_id = sl.list_id)
						WHERE sl.subscriber_id=:id";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$subscriber_id,PDO::PARAM_STR);
			return $arrResults = $cmd->queryAll();
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function updateSubscriberInList($subscriber_id, array $args)
	{
		if(empty($subscriber_id) || empty($args)) throw new Exception('Invalid data');
		try{
			$strSql = 'SELECT list_id, status FROM tbl_subscription_subscriber_list WHERE subscriber_id = :sid';
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":sid",$subscriber_id,PDO::PARAM_STR);
			$arrResults = $cmd->queryAll();
			
			$arrOrigins = array();
			foreach($arrResults as $R){
				$arrOrigins[$R['list_id']] = $R['status'];
			}

			//maching
			foreach($args as $key => $value){
				//update existing...
				if(isset($arrOrigins[$key])){
					if($args[$key] == '' || $args[$key] == null){						
						$strSql = 'DELETE FROM tbl_subscription_subscriber_list WHERE subscriber_id = :sid AND list_id = :lid'; 
						$cmd = $this->_db->createCommand($strSql);
						$cmd->bindParam(":sid",$subscriber_id,PDO::PARAM_STR);
						$cmd->bindParam(":lid",$key,PDO::PARAM_STR);
						$cmd->query();
					}
					else if($args[$key] != $arrOrigins[$key]){
						$strSql = 'UPDATE tbl_subscription_subscriber_list SET last_modified_date = :date, status = :status WHERE subscriber_id = :sid AND list_id = :lid';
						$cmd = $this->_db->createCommand($strSql);
						$cmd->bindParam(":sid",$subscriber_id,PDO::PARAM_STR);
						$cmd->bindParam(":lid",$key,PDO::PARAM_STR);
						$cmd->bindParam(":status",$args[$key],PDO::PARAM_STR);
						$cmd->bindValue(":date",date('Y-m-d H:i:s'));
						$cmd->query();
					}
				}
				else{
					if($args[$key] != '' && $args[$key] != null){
						$strSql = 'INSERT INTO tbl_subscription_subscriber_list (subscriber_id, list_id, last_modified_date, status) VALUES (:sid, :lid, :date, :status)';
						$cmd = $this->_db->createCommand($strSql);
						$cmd->bindParam(":sid",$subscriber_id,PDO::PARAM_STR);
						$cmd->bindParam(":lid",$key,PDO::PARAM_STR);
						$cmd->bindParam(":status",$args[$key],PDO::PARAM_STR);
						$cmd->bindValue(":date",date('Y-m-d H:i:s'));
						$cmd->query();
					}
				}
			}
			return 1;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function getListEmails(array $args = array(), $intPage = 1, $intLimit = MIN_ROWS_PER_PAGE)
	{
		try{
			$defaults = array(
				'list_id' => null
			);
			$args = $this->args_merge($defaults, $args);
			
			$intStart = ($intPage-1)*$intLimit;
			
			$cmd = $this->_db->createCommand('SELECT SQL_CALC_FOUND_ROWS *
 											  FROM tbl_subscription_mails
											  ORDER BY mail_id DESC
											  LIMIT :intStart, :intLimit');
			$cmd->bindParam(":intStart",$intStart,PDO::PARAM_INT);
			$cmd->bindParam(":intLimit",$intLimit,PDO::PARAM_INT);	
			$arrResults = $cmd->queryAll();
			if(!empty($arrResults)){
				$arrResults[0]['total_rows'] = $this->_db->createCommand('SELECT FOUND_ROWS();')->queryScalar();
			}
			return $arrResults;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function getEmail($mail_id)
	{
		if(empty($mail_id)) throw new Exception('Invalid data');
		try{
			$strSql = " SELECT *
						FROM tbl_subscription_mails
						WHERE mail_id=:id";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$mail_id,PDO::PARAM_STR);
			$arrResults = $cmd->queryRow();
			
			if(!empty($arrResults)){
				$arrResults['lists'] = $this->getListByEmail($mail_id);	
			}
			
			return $arrResults;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function insertEmail(array $args)
	{
		try{
			$defaults = array(
				'mail_subject' => null, 
				'mail_body' => null, 
				'mail_description' => null, 
				'mail_created_date' =>  new CDbExpression('NOW()'), 
				'mail_last_modified_date' => null
			);
			$args = $this->args_merge($defaults, $args);
			
			$cmd = $this->_db->createCommand();
			$cmd->insert('tbl_subscription_mails',$args);
			return Yii::app()->db->getLastInsertId();
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function updateEmail($mail_id, array $args)
	{
		if(empty($mail_id)) throw new Exception('Invalid data');
		try{
			$defaults = array(
				'mail_subject' => null, 
				'mail_body' => null, 
				'mail_description' => null, 
				'mail_last_modified_date' =>  new CDbExpression('NOW()')
			);
			$args = $this->args_merge($defaults, $args);
			
			$cmd = $this->_db->createCommand();
			return $cmd->update('tbl_subscription_mails',$args, 'mail_id=:id', array(':id'=>$mail_id));
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function deleteEmail($mail_id)
	{
		if(empty($mail_id)) throw new Exception('Invalid data');
		try{
			$cmd = $this->_db->createCommand();
			//delete from list
			$cmd->delete('tbl_subscription_mail_list', 'mail_id=:id', array(':id'=>$mail_id));
			//delete mails
			return $cmd->delete('tbl_subscription_mails', 'mail_id=:id', array(':id'=>$mail_id));			
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function getListByEmail($mail_id)
	{
		if(empty($mail_id)) throw new Exception('Invalid data');
		try{
			$strSql = " SELECT ml.list_id
						FROM tbl_subscription_mail_list ml INNER JOIN tbl_subscription_lists l ON (l.list_id = ml.list_id)
						WHERE ml.mail_id=:id";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$mail_id,PDO::PARAM_STR);
			return $arrResults = $cmd->queryAll();
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function updateEmailInList($mail_id, array $args)
	{
		if(empty($mail_id)) throw new Exception('Invalid data');
		try{
			//delete old items
			$cmd = $this->_db->createCommand();
			$intResult = $cmd->delete('tbl_subscription_mail_list', 'mail_id=:id', array(':id'=>$mail_id));
			
			//insert new items
			if(!empty($args)){
				$strSql = 'INSERT INTO tbl_subscription_mail_list (mail_id, list_id) VALUES ';
				$values = array();
				foreach($args as $list_id){
					$values[] = "({$mail_id}, {$list_id})";
				}
				$strSql .= implode(',', $values);
				$intResult = $this->_db->createCommand($strSql)->execute();
			}
			
			return $intResult;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
}